﻿Imports System.Data.SqlClient
Public Class ItemsDAO
    Inherits Conexion
    Dim cmd As New SqlCommand
    'Funciones
    Public Function mostrar() As DataTable
        Try
            conectar()
            cmd = New SqlCommand("items_mostrar")
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Connection = cnn
            Dim da As SqlDataReader = cmd.ExecuteReader
            Dim dt As New DataTable
            dt.Load(da)
            If dt.Rows.Count > 0 Then
                Return dt
            Else

                Return Nothing
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Advertencia", MessageBoxButtons.OK,
                          MessageBoxIcon.Warning)
            Return Nothing
        Finally
            desconectar()
        End Try
    End Function
    Public Function mostrar_por_tipo(ByVal reg As Items) As DataTable
        Try
            conectar()
            cmd = New SqlCommand("items_mostrar_por_tipo")
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Connection = cnn
            cmd.Parameters.AddWithValue("@id", reg.pTipo)
            Dim da As SqlDataReader = cmd.ExecuteReader
            Dim dt As New DataTable
            dt.Load(da)
            If dt.Rows.Count > 0 Then
                Return dt
            Else

                Return Nothing
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Advertencia", MessageBoxButtons.OK,
                          MessageBoxIcon.Warning)
            Return Nothing
        Finally
            desconectar()
        End Try
    End Function
    'Public Function mostrar_costo(ByVal reg As Items) As DataTable
    '    Try
    '        conectar()
    '        cmd = New SqlCommand("items_mostrar_costo")
    '        cmd.CommandType = CommandType.StoredProcedure
    '        cmd.Connection = cnn
    '        cmd.Parameters.AddWithValue("@id", reg.pIdProd)
    '        Dim da As SqlDataReader = cmd.ExecuteReader
    '        Dim dt As New DataTable
    '        dt.Load(da)
    '        If dt.Rows.Count > 0 Then
    '            Return dt
    '        Else

    '            Return Nothing
    '        End If
    '    Catch ex As Exception
    '        MessageBox.Show(ex.Message, "Advertencia", MessageBoxButtons.OK,
    '                      MessageBoxIcon.Warning)
    '        Return Nothing
    '    Finally
    '        desconectar()
    '    End Try
    'End Function



    Public Function mostrar_costo(ByVal reg As Items) As DataTable
        Try
            conectar()
            cmd = New SqlCommand("items_mostrar_costo")
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Connection = cnn
            cmd.Parameters.AddWithValue("@idPro", reg.pIdProd)
            Dim dr As SqlDataReader
            cmd.ExecuteNonQuery()
            dr = cmd.ExecuteReader
            Dim dt As New DataTable
            dt.Load(dr)
            Dim str_Costo As String = dt.Rows(0).Item(6).ToString
            registro_costo = str_Costo
            Return Nothing
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Advertencia", MessageBoxButtons.OK,
                          MessageBoxIcon.Warning)
            Return Nothing
        Finally
            desconectar()
        End Try
    End Function


    Public Function mostrar_peso(ByVal reg As Items) As DataTable
        Try
            conectar()
            cmd = New SqlCommand("items_mostrar_peso")
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Connection = cnn
            cmd.Parameters.AddWithValue("@id", reg.pIdProd)
            Dim dr As SqlDataReader
            cmd.ExecuteNonQuery()
            dr = cmd.ExecuteReader
            Dim dt As New DataTable
            dt.Load(dr)
            Dim str_Rol As Integer = dt.Rows(0).Item(1).ToString
            Dim str_idpro As Integer = dt.Rows(0).Item(0).ToString
            registroUltimo3 = str_Rol
            registro_idProducto = str_idpro
            Return Nothing
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Advertencia", MessageBoxButtons.OK,
                          MessageBoxIcon.Warning)
            Return Nothing
        Finally
            desconectar()
        End Try
    End Function
    Public Function mostrarTipoProducto() As DataTable
        Try
            conectar()
            cmd = New SqlCommand("Tproducto_mostrar")
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Connection = cnn
            Dim da As SqlDataReader = cmd.ExecuteReader
            Dim dt As New DataTable
            dt.Load(da)
            If dt.Rows.Count > 0 Then
                Return dt
            Else

                Return Nothing
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Advertencia", MessageBoxButtons.OK,
                          MessageBoxIcon.Warning)
            Return Nothing
        Finally
            desconectar()
        End Try
    End Function

    Public Function agregar(ByVal reg As Items) As Boolean
        Try
            conectar()
            cmd = New SqlCommand("items_agregar")
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Connection = cnn
            'Parametros
            cmd.Parameters.AddWithValue("@comp", reg.pIdProd)
            cmd.Parameters.AddWithValue("@nomb", reg.pNombre)
            cmd.Parameters.AddWithValue("@ori", reg.pOrigen)
            cmd.Parameters.AddWithValue("@pres", reg.pPresentacion)
            cmd.Parameters.AddWithValue("@pes", reg.pPeso)
            cmd.Parameters.AddWithValue("@tal", reg.pTalla)
            cmd.Parameters.AddWithValue("@costo", reg.pCosto)
            cmd.Parameters.AddWithValue("@nomco", reg.pNC)
            cmd.Parameters.AddWithValue("@idtipo", reg.pTipo)
            cmd.Parameters.AddWithValue("@accion", SqlDbType.Bit)
            cmd.Parameters("@accion").Direction = ParameterDirection.Output
            cmd.ExecuteNonQuery()
            Return cmd.Parameters("@accion").Value
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Advertencia", MessageBoxButtons.OK,
                        MessageBoxIcon.Warning)
            Return False
        Finally
            desconectar()
        End Try
    End Function

    Public Function actualizar(ByVal reg As Items) As Boolean
        Try
            conectar()
            cmd = New SqlCommand("items_actualizar")
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Connection = cnn
            'Parametros
            cmd.Parameters.AddWithValue("@idprod", reg.pIdProd)
            cmd.Parameters.AddWithValue("@nomb", reg.pNombre)
            cmd.Parameters.AddWithValue("@ori", reg.pOrigen)
            cmd.Parameters.AddWithValue("@pres", reg.pPresentacion)
            cmd.Parameters.AddWithValue("@pes", reg.pPeso)
            cmd.Parameters.AddWithValue("@tal", reg.pTalla)
            cmd.Parameters.AddWithValue("@costo", reg.pCosto)
            cmd.Parameters.AddWithValue("@idtipo", reg.pTipo)
            cmd.Parameters.AddWithValue("@accion", SqlDbType.Bit)
            cmd.Parameters("@accion").Direction = ParameterDirection.Output
            cmd.ExecuteNonQuery()
            Return cmd.Parameters("@accion").Value
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Advertencia", MessageBoxButtons.OK,
                        MessageBoxIcon.Warning)
            Return False
        Finally
            desconectar()
        End Try
    End Function

    Public Function eliminar(ByVal reg As Items) As Boolean
        Try
            conectar()
            cmd = New SqlCommand("items_eliminar")
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Connection = cnn
            'Parametros
            cmd.Parameters.AddWithValue("@idprod", reg.pIdProd)
            cmd.Parameters.AddWithValue("@nomb", reg.pNombre)
            cmd.Parameters.AddWithValue("@ori", reg.pOrigen)
            cmd.Parameters.AddWithValue("@pres", reg.pPresentacion)
            cmd.Parameters.AddWithValue("@pes", reg.pPeso)
            cmd.Parameters.AddWithValue("@tal", reg.pTalla)
            cmd.Parameters.AddWithValue("@costo", reg.pCosto)
            cmd.Parameters.AddWithValue("@idtipo", reg.pTipo)
            cmd.Parameters.AddWithValue("@accion", SqlDbType.Bit)
            cmd.Parameters("@accion").Direction = ParameterDirection.Output
            cmd.ExecuteNonQuery()
            Return cmd.Parameters("@accion").Value
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Advertencia", MessageBoxButtons.OK,
                        MessageBoxIcon.Warning)
            Return False
        Finally
            desconectar()
        End Try
    End Function
End Class
